#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : tmp_check.py
@Author : shenj
@time : 2025/5/16 9:56
@func :
"""
import logging
import os
import time
from datetime import datetime
from typing import List

import oracledb
import pandas as pd

from com.cn.for_cdc.check_data.async_batch_query import self_constanct
from com.cn.for_cdc.common.cdc_conmons import oracle_adm_connections
from com.cn.for_cdc.common.log_helper import configure_logging


# 然后再配置你的日志系统


def check_sql_queries(config, sql_statements):
    try:
        # 建立数据库连接
        connection = oracledb.connect(
            user=config["user"],
            password=config["password"],
            dsn=config['dsn']
        )

        # 遍历所有SQL语句
        # for sql in sql_statements:
        for index, sql in enumerate(sql_statements, start=1):
            # logging.info(f"序号: {index}, {datetime.now().strftime('%Y-%m-%d %H:%M:%S')} 开始执行SQl:\n{sql}")
            start_time = time.time()
            # sql = sql.strip().replace(';', '')
            sql = str(sql).strip().replace(';', '') if sql is not None else ''
            if sql.strip() != '':
                sql = sql.replace('${BU_ID}', "'1-7V7X'").replace('${BU_NAME}', 'ICIBE').replace('BU_CODE = ICIBE', "BU_CODE = 'ICIBE'")
                # sql = sql.replace('${BU_ID}', "'1-7V7V'").replace('${BU_NAME}', 'ICINL').replace('BU_CODE = ICINL', "BU_CODE = 'ICINL'")
                with connection.cursor() as cursor:
                    try:
                        # 修改后（安全版本）
                        try:
                            safe_sql = sql.replace('\xa0', ' ')  # 替换不换行空格
                            logging.info(f"序号: {index},查询SQL:\n{safe_sql}")
                        except UnicodeError:
                            # 如果仍然出错，使用更安全的编码方式
                            safe_sql = sql.encode('utf-8', errors='replace').decode('utf-8')
                            logging.info(f"序号: {index}, SQL(编码后):\n{safe_sql}")

                        # 执行查询
                        cursor.execute(sql)
                        rows: List[tuple] = cursor.fetchone()
                        # 检查是否存在结果（使用fetchone提高效率）
                        end_time = time.time()
                        if rows:
                            logging.info(f"序号: {index},查询耗时:" + "{:.2f}s".format(end_time - start_time))
                            logging.info(f"序号: {index},查询结果:" + str(rows))
                            logging.info("-"*100)
                        else:
                            # logging.info(f"序号: {index}, No issue,{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}查询耗时:"+"{:.2f}s".format(end_time - start_time))
                            pass
                        pass
                    except oracledb.Error as e:
                        logging.info(f"序号: {index} Error executing SQl:\n{sql}\nError: {e}\n")
                        logging.info("-" * 100)
                        continue  # 继续执行下一条SQL
            else:
                logging.info(f"序号: {index}, {datetime.now().strftime('%Y-%m-%d %H:%M:%S')} SQl is empty:\n")
                continue
            # break

    finally:
        if 'connection' in locals() and connection is not None:
            connection.close()



def main():
    configure_logging()

    # starttime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # logging.info(starttime)

    # 配置数据库连接信息（根据实际情况修改）
    config= oracle_adm_connections['icibe_pro']
    logging.info(config)


    SQL_STATEMENTS=[
        """

SELECT COUNT(0) FROM ( SELECT /*+ parallel(8) */a.row_id,a.ZIPCODE,a.ADDR,a.ADDR_NAME,a.CITY
FROM siebel.s_contact@sbl b, siebel.s_con_addr@sbl e, siebel.s_addr_per@sbl a,TMP_${BU_NAME}_CARD_MAY12 T
WHERE e.addr_per_id = a.row_id
		AND b.row_id = e.contact_id
		AND b.bu_id =  ${BU_ID}
        AND T.CONTACT_ID = b.row_id
AND a.last_upd <  sysdate -1 / 12
MINUS
SELECT /*+ parallel(8)  */ADDR_PER_ID,ZIPCODE,ADDR,ADDR_NAME,CITY FROM STG_B_ADDR_PER WHERE BU_CODE = ${BU_NAME})t;
        """


    ]
    # 执行检查
    start = time.time()
    check_sql_queries(config, SQL_STATEMENTS)
    end = time.time()
    logging.info("整体耗时:{:.2f}s".format(end - start))





if __name__ == "__main__":
    main()
